package com.weixingtu.commuterCar.dao.impl;

import com.weixingtu.commuterCar.bean.Message;
import com.weixingtu.commuterCar.bean.Site;
import com.weixingtu.commuterCar.dao.SiteDao;
import com.weixingtu.commuterCar.util.DruidUtil;
import com.weixingtu.commuterCar.util.JSONUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class SiteDaoMysql implements SiteDao {
    /**
     * 用于查询数据库中的所有站点信息
     */

    public static final String SQL_FIND_ALL = " SELECT * FROM site ORDER BY obj_id+0 ";
    /**
     * 用于查询数据库中的所属路线站点信息
     */
    public static final String SQL_FIND_BY_LINE = " SELECT * FROM site WHERE line_id=? ORDER BY obj_id+0 ";
    /**
     * 根据路线id和index查询站点
     */
    public static final String SQL_FIND_Site = " SELECT * FROM site WHERE line_id=? and site_id=? ";

    /**
     * 用于查询所有站点
     *
     * @return 站点的集合
     */
    @Override
    public List<Site> findAll() {
        List<Site> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
//        if(conn == null) return null;
        //2.    预编译SQL语句
        try {
            if(conn == null) {
                System.out.println("数据库连接失败");
            } else{
                System.out.println("数据库连接成功");
            }
            state = conn.prepareStatement(SQL_FIND_ALL);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                //  int objId, int siteId, String siteName, double lat, double lng, double costTime, int lineId, String lineName
                int objId = Integer.parseInt(result.getString("obj_id"));
                int siteId = Integer.parseInt(result.getString("site_id"));
                String siteName = result.getString("site_name");
                double lat = Double.parseDouble(result.getString("lat"));
                double lng = Double.parseDouble(result.getString("lng"));
                double costTime = Double.parseDouble(result.getString("time_to_next_site"));
                int lineId = Integer.parseInt(result.getString("line_id"));
                String lineName = result.getString("line_name");
                Site site = new Site(objId, siteId, siteName, lat, lng, costTime, lineId, lineName);
                data.add(site);
            }
            return data;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 根据路线查询站点
     *
     * @param lineId
     * @return 所属路线的站点的集合
     */
    @Override
    public List<Site> findByLine(int lineId) {
        List<Site> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            if(conn == null) {
                System.out.println("数据库连接失败");
            } else{
                System.out.println("数据库连接成功");
            }
            state = conn.prepareStatement(SQL_FIND_BY_LINE);
            String s = String.valueOf(lineId);
            if(s==null || s.length()==0) return null;
            //3.    填充参数(可选)
            state.setString(1, s);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                //  int objId, int siteId, String siteName, double lat, double lng, double costTime, int lineId, String lineName
                int objId = Integer.parseInt(result.getString("obj_id"));
                int siteId = Integer.parseInt(result.getString("site_id"));
                String siteName = result.getString("site_name");
                double lat = Double.parseDouble(result.getString("lat"));
                double lng = Double.parseDouble(result.getString("lng"));
                double costTime = Double.parseDouble(result.getString("time_to_next_site"));
//                int lineId = Integer.parseInt(result.getString("line_id"));
                String lineName = result.getString("line_name");
                Site site = new Site(objId, siteId, siteName, lat, lng, costTime, lineId, lineName);
                data.add(site);
            }
            return data;
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }
        return null;
    }

    /**
     * 查询所有起始站点名称
     *
     * @return 起始站点名称
     */
    @Override
    public Map<String, String> getStartAndEnd() {
        List<Site> line1 = new ArrayList<>();
        List<Site> line2 = new ArrayList<>();
        List<Site> line3 = new ArrayList<>();
        List<Site> line4 = new ArrayList<>();
        List<Site> line5 = new ArrayList<>();
        List<Site> line6 = new ArrayList<>();
        List<Site> line7 = new ArrayList<>();
        List<Site> line8 = new ArrayList<>();
        List<Site> data = new ArrayList<>();
        //1.    获取数据库的连接
        Connection conn = DruidUtil.getConnection();
        PreparedStatement state = null;
        ResultSet result = null;
        //2.    预编译SQL语句
        try {
            if(conn == null) {
                System.out.println("数据库连接失败");
            } else{
                System.out.println("数据库连接成功");
            }
            state = conn.prepareStatement(SQL_FIND_ALL);
            //4.    执行SQL语句
            result = state.executeQuery();
            //5.    获取执行的结果
            while (result.next()) {
                //  int objId, int siteId, String siteName, double lat, double lng, double costTime, int lineId, String lineName
                int objId = Integer.parseInt(result.getString("obj_id"));
                int siteId = Integer.parseInt(result.getString("site_id"));
                String siteName = result.getString("site_name");
                double lat = Double.parseDouble(result.getString("lat"));
                double lng = Double.parseDouble(result.getString("lng"));
                double costTime = Double.parseDouble(result.getString("time_to_next_site"));
                int lineId = Integer.parseInt(result.getString("line_id"));
                String lineName = result.getString("line_name");
                Site site = new Site(objId, siteId, siteName, lat, lng, costTime, lineId, lineName);
                String s = String.valueOf(lineId);
                if("1".equals(s)) {
                    line1.add(site);
                }else if("2".equals(s)) {
                    line2.add(site);
                }else if("3".equals(s)) {
                    line3.add(site);
                }else if("4".equals(s)) {
                    line4.add(site);
                }else if("5".equals(s)) {
                    line5.add(site);
                }else if("6".equals(s)) {
                    line6.add(site);
                }else if("7".equals(s)) {
                    line7.add(site);
                }else if("8".equals(s)) {
                    line8.add(site);
                }
            }
            //6.    资源的释放
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DruidUtil.close(conn, state, result);
        }

        Map<String, String> map = new HashMap<>();
        if (line1 != null && line1.size() != 0) {
            String q1 = line1.get(0).getSiteName();
            if(q1!=null && q1.length()!=0) {
                map.put("q1", q1);
            }
            String z1 = line1.get(line1.size() - 1).getSiteName();
            if(z1!=null && z1.length()!=0) {
                map.put("z1", z1);
            }

            String tj1 = new String(" ");
            for (Site site : line1) {
                String tj = site.getSiteName();
                if(tj!=null && tj.length()!=0) {
                    tj1 = tj1 + tj + " | ";
                }
            }
            map.put("tj1", tj1);

        }
        if (line2 != null && line2.size() != 0) {
            String q2 = line2.get(0).getSiteName();
            if(q2 != null && q2.length()!=0) {
                map.put("q2", q2);
            }
            String z2 = line2.get(line2.size() - 1).getSiteName();
            if(z2!=null && z2.length()!=0) {
                map.put("z2", z2);
            }

            String tj2 = new String(" ");
            for (Site site : line2) {
                String tj = site.getSiteName();
                if(tj!=null && tj.length()!=0) {
                    tj2 = tj2 + tj + " | ";
                }
            }
            map.put("tj2", tj2);
        }
        if (line3 != null && line3.size() != 0) {
            String q3 = line3.get(0).getSiteName();
            if(q3 != null && q3.length()!=0) {
                map.put("q3", q3);
            }
            String z3 = line3.get(line3.size() - 1).getSiteName();
            if(z3!=null && z3.length()!=0) {
                map.put("z3", z3);
            }

            String tj3 = new String(" ");
            for (Site site : line3) {
                String tj = site.getSiteName();
                if(tj!=null && tj.length()!=0) {
                    tj3 = tj3 + tj + " | ";
                }
            }
            map.put("tj3", tj3);
        }
        if (line4 != null && line4.size() != 0) {
            String q4 = line4.get(0).getSiteName();
            if(q4 != null && q4.length()!=0) {
                map.put("q4", q4);
            }
            String z4 = line4.get(line4.size() - 1).getSiteName();
            if(z4!=null && z4.length()!=0) {
                map.put("z4", z4);
            }

            String tj4 = new String(" ");
            for (Site site : line4) {
                String tj = site.getSiteName();
                if(tj!=null && tj.length()!=0) {
                    tj4 = tj4 + tj + " | ";
                }
            }
            map.put("tj4", tj4);
        }
        if (line5 != null && line5.size() != 0) {
            String q5 = line5.get(0).getSiteName();
            if(q5 != null && q5.length()!=0) {
                map.put("q5", q5);
            }
            String z5 = line5.get(line5.size() - 1).getSiteName();
            if(z5!=null && z5.length()!=0) {
                map.put("z5", z5);
            }

            String tj5 = new String(" ");
            for (Site site : line5) {
                String tj = site.getSiteName();
                if(tj!=null && tj.length()!=0) {
                    tj5 = tj5 + tj + " | ";
                }
            }
            map.put("tj5", tj5);
        }
        if (line6 != null && line6.size() != 0) {
            String q6 = line6.get(0).getSiteName();
            if(q6 != null && q6.length()!=0) {
                map.put("q6", q6);
            }
            String z6 = line6.get(line6.size() - 1).getSiteName();
            if(z6!=null && z6.length()!=0) {
                map.put("z6", z6);
            }

            String tj6 = new String(" ");
            for (Site site : line6) {
                String tj = site.getSiteName();
                if(tj!=null && tj.length()!=0) {
                    tj6 = tj6 + tj + " | ";
                }
            }
            map.put("tj6", tj6);
        }
        if (line7 != null && line7.size() != 0) {
            String q7 = line7.get(0).getSiteName();
            if(q7 != null && q7.length()!=0) {
                map.put("q7", q7);
            }
            String z7 = line7.get(line7.size() - 1).getSiteName();
            if(z7!=null && z7.length()!=0) {
                map.put("z7", z7);
            }

            String tj7 = new String(" ");
            for (Site site : line7) {
                String tj = site.getSiteName();
                if(tj!=null && tj.length()!=0) {
                    tj7 = tj7 + tj + " | ";
                }
            }
            map.put("tj7", tj7);
        }
        if (line8 != null && line8.size() != 0) {
            String q8 = line8.get(0).getSiteName();
            if(q8 != null && q8.length()!=0) {
                map.put("q8", q8);
            }
            String z8 = line8.get(line8.size() - 1).getSiteName();
            if(z8!=null && z8.length()!=0) {
                map.put("z8", z8);
            }

            String tj8 = new String(" ");
            for (Site site : line8) {
                String tj = site.getSiteName();
                if(tj!=null && tj.length()!=0) {
                    tj8 = tj8 + tj + " | ";
                }
            }
            map.put("tj8", tj8);
        }
        if (map == null || map.size() == 0) {
            return null;
        }
        return map;

    }
}
